--drop FUNCTION GetInStoreBillID 
CREATE FUNCTION GetInStoreBillID ()
    RETURNS VARCHAR(10)
AS
BEGIN
	declare @date DATETIME;
   declare @prix varchar(7);
   declare  @maxID  int;
   declare  @result varchar(10);
   select @date=dt FROM VGetDate;
   set @prix = CONVERT(char(7),@date,120);
   set @prix = 'C'+substring(@prix,3,7) + '-';
   select @maxID = isnull(MAX(substring(BillID,8,3)),0) + 1 from dbo.InStoreBill 
 where BillID like @prix+'%';
	set @result = @prix + right('00' +  convert(varchar(3),@maxID),3);
	return @result;
END
go


--drop FUNCTION GetOutStoreBillID 
CREATE FUNCTION GetOutStoreBillID ()
    RETURNS VARCHAR(10)
AS
BEGIN
	declare @date DATETIME;
   declare @prix varchar(7);
   declare  @maxID  int;
   declare  @result varchar(10);
   select @date=dt FROM VGetDate;
   set @prix = CONVERT(char(7),@date,120);
   set @prix = 'C'+replace(@prix,'-','');
   select @maxID = isnull(MAX(substring(BillID,8,3)),0) + 1 from dbo.OutStoreBill 
 where BillID like @prix+'%';
	set @result = @prix + right('00' +  convert(varchar(3),@maxID),3);
	return @result;
END
go


--drop FUNCTION GetPayApplyBillID 
CREATE FUNCTION GetPayApplyBillID ()
    RETURNS VARCHAR(10)
AS
BEGIN
	declare @date DATETIME;
   declare @prix varchar(7);
   declare  @maxID  int;
   declare  @result varchar(10);
   select  @date=dt FROM VGetDate;
   set @prix = CONVERT(char(7),@date,120);
   set @prix = 'F'+replace(@prix,'-','');
   select @maxID = isnull(MAX(substring(PayApplyBillID,8,3)),0) + 1 from dbo.PayApplyBill 
 where PayApplyBillID like @prix+'%';
	set @result = @prix + right('00' +  convert(varchar(3),@maxID),3);
	return @result;
END
go

--drop FUNCTION GetPayApplyBillDtlSumAmount 
CREATE FUNCTION GetPayApplyBillDtlSumAmount (@InvoiceNo varchar(20) )
    RETURNS numeric(16,2)
AS
begin
  declare @result numeric(16,2);
		select @result = isnull(SUM(Amount),0) from  dbo.PayApplyBillDtl where InvoiceNo = @InvoiceNo;
		return @result;
end